<?php 


// 插入
insert into stu (hobby) value('电影');

insert into stu (name,sex,qq) value('马志斌','男','123123213'),('宝强','男','45345354'),('宝强','男','45345354'),('宝强','男','45345354'),('宝强','男','45345354');

// 更新
update stu set sex='女' where name='宝强';

update stu set sex='男',age=20 where name='宝强';

update stu set sex='女' where id<7;

update stu set age=age+1 where age >=20;


// 删除
delete from stu order by age desc limit 1;


// 修改表名
alter table stu1 rename stu2;

// 修改字段
alter table stu2 change name name1 varchar(30) no null default '';

//修改
alter table stu2 modify name1 char(10);

// 增加字段
 alter table stu2 add qq varchar(11) not null;

 alter table stu2 modify id int(10) not null;

 alter table stu2 drop primary key;



select avg(score),class from stu group by class;

select count(id),class from stu group by class;


子查询
 select * from stu where class= (select class from stu where name='宝宝');





create table class (
	id int(3) not null,
	cname varchar(20)
	);
course

create table course (
	id int(3) not null,
	coname varchar(20)
	);


create table stu_course (
	uid int(3) not null,
	coid int(3) not null
	);

select * from stu,class; 

// di ka er ji
select * from stu,class where stu.cid=class.id;


// di ka er ji jai where
select * from stu t1,class t2 where t1.cid=t2.id;



// 找出1班的同学
select * from stu t1 inner join class t2 on t1.cid=t2.id where t2.cname='P201601';



// 找出宝宝报名的课程
select * from stu t1 inner join stu_course t2 on t1.id=t2.uid inner join course t3 on t3.id=t2.coid where t1.name='宝宝';


// 左联
select * from stu t1 left join class t2 on t1.cid=t2.id;

// 一班报了哪些课程


// select * from stu t1 left join class t2 on t1.cid=t2.id left 


select * from stu t1 left join stu_course t2 on t1.id=t2.uid left join course t3 on t3.id=t2.coid left join class t4 on t1.cid=t4.id where t4.cname='P201601';



select distinct t3.coname,t4.cname from stu t1 left join stu_course t2 on t1.id=t2.uid left j
oin course t3 on t3.id=t2.coid left join class t4 on t1.cid=t4.id where t4.cname='P201601';


select * from stu t1 inner join stu t2 on t1.cid=t2.cid where t1.name='小张';




 ?>